package Opgave9_4;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StatementPrepared {
	
	public StatementPrepared() {
		CreateDatabase database = new CreateDatabase();
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			database.deleteDB();
			database.makeDB();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		preparedStatementInputTest();
		preparedStatementOutputTest();
	}
	
	private void preparedStatementInputTest() {
		String firstName = "Simon", lastName = "Langhoff";
		long time = System.currentTimeMillis();
		for (int id = 1000; id < 2000; id++) {
			Connection con = null;
			PreparedStatement stmt = null;
			try {
				con = DriverManager.getConnection("jdbc:derby:PersonDB;");
				String sql = "INSERT INTO PERSON VALUES(?, ?, ?)";
				stmt = con.prepareStatement(sql);
				stmt.setInt(1, id);
				stmt.setString(2, firstName);
				stmt.setString(3, lastName);
				stmt.executeUpdate();
			} catch(SQLException e) {
				System.out.println(e);
			} finally {
				con = null;
				stmt = null;
			}
		}
		long totalTime = System.currentTimeMillis() - time;
		System.out.println("Prepared statement input-test took: " + totalTime + " miliseconds.");
	}


	private void preparedStatementOutputTest() {
		long time = System.currentTimeMillis();
		for (int id = 1000; id < 2000; id++) {
			Connection con = null;
			PreparedStatement stmt = null;
			ResultSet rs = null;
			try {
				con = DriverManager.getConnection("jdbc:derby:PersonDB;");			
				String sql = "SELECT * FROM PERSON WHERE PERSON_PK = ?";

				stmt = con.prepareStatement(sql);
				stmt.setInt(1, id);
				rs = stmt.executeQuery();
			} catch(SQLException e) {
				System.out.println(e);
			} finally {
				con = null;
				stmt = null;
				rs = null;
			}
		}
		long totalTime = System.currentTimeMillis() - time;
		System.out.println("Prepared statement output-test took: " + totalTime + " miliseconds.");		
	}
}
